# @Time    : 2019/8/15 16:37
# @Author  : patrick
# @File    : mysqlHelper.py
# @Software: PyCharm
import pymysql.cursors
import pymysql


class MysqlHelper:
    def __init__(self):
        self.host = '10.22.31.50'
        self.user = 'ods_cq_read'
        self.password = '02mjyis5'
        self.db = 'lifeasia'
        self.port = 6033

    def __getCursor(self):
        try:
            self.conn = pymysql.connect(
                host=self.host,
                port=int(self.port),
                user=self.user,
                password=self.password,
                database=self.db,
                cursorclass=pymysql.cursors.DictCursor,
                charset="utf8"
            )
        except Exception as e:
            print("DataBase Error : %s" % e)
        finally:
            self.cursor = self.conn.cursor()

    def excuteQuery(self, sql):
        try:
            self.__getCursor()
            # print("开启数据库连接。")
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception as e:
            print("Query Error : %s" % e)
        finally:
            self.cursor.close()
            self.conn.close()
            # print("关闭数据库连接。")

    def queryPolicy(self, policyNo):
        sql = "SELECT stg_zchrpf." \
              "REFNO," \
              "stg_zchrpf." \
              "CHDRNUM," \
              "stg_letcpf." \
              "LETTYPE," \
              "stg_chdrpf." \
              "STATCODE," \
              "stg_chdrpf." \
              "BILLFREQ " \
              "FROM stg_zchrpf LEFT JOIN stg_letcpf " \
              "ON stg_zchrpf.CHDRNUM = stg_letcpf." \
              "CHDRNUM LEFT JOIN stg_chdrpf " \
              "ON stg_zchrpf.CHDRNUM = stg_chdrpf.CHDRNUM " \
              "WHERE stg_zchrpf.REFNO = '%s'" % (policyNo)
        # print(sql)
        result = self.excuteQuery(sql)

        return result


if __name__ == '__main__':
    conn = MysqlHelper()
    num = 'LA0100000105521'
    print(conn.queryPolicy(num))
